package no.sogl.importer;

import java.io.File;
import java.io.FileOutputStream;
import java.util.Date;
import java.util.HashMap;
import java.util.Map;

import no.sogl.domain.Person;
import no.sogl.domain.PersonTag;
import no.sogl.domain.Team;
import no.sogl.reference.Gender;
import no.sogl.util.ExcelHelper;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Row;
import org.joda.time.DateTime;
import org.joda.time.format.DateTimeFormat;
import org.springframework.context.support.AbstractApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.util.StringUtils;

public class MemberExporter {
    private static final Log log = LogFactory.getLog(MemberExporter.class);
    private static AbstractApplicationContext ctx;
    private final boolean teamPerSheet = false;

    public static void main(String[] args) throws Exception {
        log.info("Starting exporter");
        ctx = new ClassPathXmlApplicationContext(new String[] { "classpath:META-INF/spring/applicationContext.xml" });
        ctx.registerShutdownHook();
        MemberExporter exporter = ctx.getBean(MemberExporter.class);
        //exporter.exportN3MemberSince();
        // exporter.exportN3People();
        exporter.exportPeople();
        // exporter.exportPeopleSelection();
    }

    @Transactional
    public void exportPeopleSelection() throws Exception {
        HSSFWorkbook wb = new HSSFWorkbook();
        Map<String, CellStyle> styles = ExcelHelper.createStyles(wb);
        HSSFSheet sheet = wb.createSheet("Members");
        createHeaders(styles, sheet);
        int rowNo = 0;
        for (Person person : Person.findPeopleByBirthYearBetween(1994, 2009).getResultList())
            addRow(person, sheet, ++rowNo, styles);
        wb.write(new FileOutputStream(new File("/Users/espenwest/Documents/sogl/medlemsutvalg"
                + DateTimeFormat.forPattern("yyyyMMdd").print(new DateTime()) + ".xls")));
    }

    @Transactional
    public void exportPeople() throws Exception {
        HSSFWorkbook wb = new HSSFWorkbook();
        Map<String, CellStyle> styles = ExcelHelper.createStyles(wb);
        Map<String, HSSFSheet> sheets = new HashMap<String, HSSFSheet>();
        Map<String, Integer> rowNos = new HashMap<String, Integer>();
        for (Team team : Team.findAllTeams()) {
            HSSFSheet sheet = wb.createSheet(team.getName());
            sheets.put(team.getName(), sheet);
            createHeaders(styles, sheet);
            rowNos.put(team.getName(), 1);
        }
        HSSFSheet sheet = wb.createSheet("Alle");
        sheets.put("Alle", sheet);
        createHeaders(styles, sheet);
        rowNos.put("Alle", 1);
        
        for (Person person : Person.findAllPeople()) {
            if ( person == null ) continue;
            sheet = sheets.get("Alle");
            Integer rowNo = rowNos.get("Alle");
            rowNos.put("Alle", rowNo + 1);
            addRow(person, sheet, rowNo, styles);
            for (PersonTag tag : person.getTags()) {
                sheet = sheets.get(tag.getTag().getName());
                if (sheet == null) continue;
                rowNo = rowNos.get(tag.getTag().getName());
                rowNos.put(tag.getTag().getName(), rowNo + 1);
                addRow(person, sheet, rowNo, styles);
            }
        }

        wb.write(new FileOutputStream(new File("/Users/espenwest/Documents/sogl/medlemseksport "
                + DateTimeFormat.forPattern("yyyyMMdd").print(new DateTime()) + ".xls")));
    }

    @Transactional
    public void exportN3People() throws Exception {
        HSSFWorkbook wb = new HSSFWorkbook();
        Map<String, CellStyle> styles = ExcelHelper.createStyles(wb);
        int rowNo = 1;
        HSSFSheet sheet = wb.createSheet("n3sport");
        createN3sportHeaders(styles, sheet);

        for (Person person : Person.findAllPeople()) {
            if (person.getMembership() != null) continue;
            if (person.getBirthDay() == null) continue;
            addN3Row(person, sheet, rowNo++, styles);
        }

        wb.write(new FileOutputStream(new File("/Users/espenwest/Documents/sogl/n3eksport"
                + DateTimeFormat.forPattern("yyyyMMdd").print(new DateTime()) + ".xls")));
    }

    @Transactional
    public void exportN3MemberSince() throws Exception {
        Date ifAfter = new DateTime().withDate(2012, 1, 1).toDate();
        Date setDate = new DateTime().withDate(2011, 12, 31).toDate();
        HSSFWorkbook wb = new HSSFWorkbook();
        Map<String, CellStyle> styles = ExcelHelper.createStyles(wb);
        int rowNo = 1;
        HSSFSheet sheet = wb.createSheet("n3sport");
        createN3MiniHeaders(styles, sheet);

        for (Person person : Person.findAllPeople()) {
            if (person.getMembership() == null || person.getMembership().getMemberSince() == null
                    || person.getMembership().getMemberSince().before(ifAfter)) continue;
            addN3MiniRow(person, setDate, sheet, rowNo++, styles);
        }

        wb.write(new FileOutputStream(new File("/Users/espenwest/Documents/sogl/n3minieksport"
                + DateTimeFormat.forPattern("yyyyMMdd").print(new DateTime()) + ".xls")));
    }

    private void addRow(Person person, HSSFSheet sheet, int rowNo, Map<String, CellStyle> styles) {
        Row row = sheet.createRow(rowNo);
        ExcelHelper.createCell(row, 0, person.getFirstName(), styles.get("left"));
        ExcelHelper.createCell(row, 1, person.getLastName(), styles.get("left"));
        ExcelHelper.createCell(row, 2, person.getAddress(), styles.get("left"));
        ExcelHelper.createCell(row, 3, person.getZipCode(), styles.get("right"));
        ExcelHelper.createCell(row, 4, person.getCity(), styles.get("left"));
        ExcelHelper.createCell(row, 5, person.getBirthDay(), styles.get("date"));
        ExcelHelper.createCell(row, 6, person.getBirthYear(), null);
        ExcelHelper.createCell(row, 7, person.getPhone() != null ? person.getPhone() : "", styles.get("left"));
        ExcelHelper.createCell(row, 8, person.getMobile() != null ? person.getMobile() : "", styles.get("left"));
        ExcelHelper.createCell(row, 9, person.getEmail(), styles.get("left"));
        ExcelHelper.createCell(row, 10, person.getParentNames(), styles.get("left"));
        if (person.getTags().size() > 0) ExcelHelper.createCell(row, 11,
            StringUtils.collectionToCommaDelimitedString(person.getTags()), styles.get("left"));
        else ExcelHelper.createCell(row, 11, "", styles.get("left"));
        if (person.getMembership() != null) ExcelHelper.createCell(row, 12, "J", styles.get("left"));
        else ExcelHelper.createCell(row, 12, "N", styles.get("left"));
        ExcelHelper.createCell(row, 13, person.getMembership() != null && person.getMembership().getMemberSince() != null
                ? person.getMembership().getMemberSince() : null, styles.get("date"));
        boolean oppmannliste = false;
        for (PersonTag personTag : person.getTags()) {
            if (personTag.getTag().getName().matches("(?is).*oppmann.*")) oppmannliste = true;
        }
        ExcelHelper.createCell(row, 14, oppmannliste ? "J" : "N", styles.get("left"));
        ExcelHelper.createCell(row, 15, person.getGender() == Gender.Female ? "K" : "M", styles.get("left"));
    }

    private void createHeaders(Map<String, CellStyle> styles, HSSFSheet sheet) {
        Row row = sheet.createRow(0);
        row.setHeightInPoints(16);
        ExcelHelper.createCell(row, 0, "Fornavn", styles.get("header"));
        sheet.setColumnWidth(0, 15 * 256);
        ExcelHelper.createCell(row, 1, "Etternavn", styles.get("header"));
        sheet.setColumnWidth(1, 15 * 256);
        ExcelHelper.createCell(row, 2, "Adresse", styles.get("header"));
        sheet.setColumnWidth(2, 15 * 256);
        ExcelHelper.createCell(row, 3, "Postnr", styles.get("header"));
        sheet.setColumnWidth(3, 11 * 256);
        ExcelHelper.createCell(row, 4, "Sted", styles.get("header"));
        sheet.setColumnWidth(4, 12 * 256);
        ExcelHelper.createCell(row, 5, "Født", styles.get("header"));
        sheet.setColumnWidth(5, 12 * 256);
        ExcelHelper.createCell(row, 6, "År", styles.get("header"));
        sheet.setColumnWidth(6, 8 * 256);
        ExcelHelper.createCell(row, 7, "Telefon", styles.get("header"));
        sheet.setColumnWidth(7, 15 * 256);
        ExcelHelper.createCell(row, 8, "Mobil", styles.get("header"));
        sheet.setColumnWidth(8, 15 * 256);
        ExcelHelper.createCell(row, 9, "E-post", styles.get("header"));
        sheet.setColumnWidth(9, 30 * 256);
        ExcelHelper.createCell(row, 10, "Foresatte", styles.get("header"));
        sheet.setColumnWidth(10, 25 * 256);
        ExcelHelper.createCell(row, 11, "Tags", styles.get("header"));
        sheet.setColumnWidth(11, 25 * 256);
        ExcelHelper.createCell(row, 12, "Medlem?", styles.get("header"));
        sheet.setColumnWidth(12, 14 * 256);
        ExcelHelper.createCell(row, 13, "Registrert", styles.get("header"));
        sheet.setColumnWidth(13, 17 * 256);
        ExcelHelper.createCell(row, 14, "I oppmannliste?", styles.get("header"));
        sheet.setColumnWidth(14, 20 * 256);
        ExcelHelper.createCell(row, 15, "Kjønn", styles.get("header"));
        sheet.setColumnWidth(15, 3 * 256);
    }

    private void addN3Row(Person person, HSSFSheet sheet, int rowNo, Map<String, CellStyle> styles) {
        Row row = sheet.createRow(rowNo);
        ExcelHelper.createCell(row, 0, person.getMembership() != null ? person.getMembership().getN3sportId() : "-1",
            styles.get("right"));
        ExcelHelper.createCell(row, 1, person.getGender() == Gender.Female ? "K" : "M", styles.get("left"));
        ExcelHelper.createCell(row, 2, person.getFirstName(), styles.get("left"));
        ExcelHelper.createCell(row, 3, person.getLastName(), styles.get("left"));
        ExcelHelper.createCell(row, 4, person.getBirthDay(), styles.get("date"));
        ExcelHelper.createCell(row, 5, person.getZipCode(), styles.get("right"));
        ExcelHelper.createCell(row, 6, person.getAddress(), styles.get("left"));
        ExcelHelper.createCell(row, 7, person.getCity(), styles.get("left"));
        ExcelHelper.createCell(row, 8, person.getEmail() != null ? person.getEmail().split(",")[0] : "", styles.get("left"));
        ExcelHelper.createCell(row, 9, person.getPhone() != null ? person.getPhone().split(",")[0] : "", styles.get("left"));
        ExcelHelper.createCell(row, 10, person.getMobile() != null ? person.getMobile().split(",")[0] : "", styles.get("left"));
        ExcelHelper.createCell(row, 11, "Enkeltmedlem", styles.get("left"));
        ExcelHelper.createCell(row, 12, "1", styles.get("left"));
        ExcelHelper.createCell(row, 13, person.getMembership() != null ? person.getMembership().getMemberSince()
                : new DateTime().withDate(2011, 12, 31).toDate(), styles.get("left"));
        ExcelHelper.createCell(row, 14, "Strømmen og Lillestrøm Skiklubb", styles.get("left"));
        ExcelHelper.createCell(row, 15, person.getAddress(), styles.get("left"));
        ExcelHelper.createCell(row, 16, person.getEmail() + "\n", styles.get("left"));

    }

    private void createN3sportHeaders(Map<String, CellStyle> styles, HSSFSheet sheet) {
        Row row = sheet.createRow(0);
        row.setHeightInPoints(16);
        ExcelHelper.createCell(row, 0, "n3sportid", styles.get("header"));
        sheet.setColumnWidth(0, 15 * 256);
        ExcelHelper.createCell(row, 1, "Kjønn", styles.get("header"));
        sheet.setColumnWidth(1, 3 * 256);
        ExcelHelper.createCell(row, 2, "Fornavn", styles.get("header"));
        sheet.setColumnWidth(2, 15 * 256);
        ExcelHelper.createCell(row, 3, "Etternavn", styles.get("header"));
        sheet.setColumnWidth(3, 15 * 256);
        ExcelHelper.createCell(row, 4, "Fødselsdato", styles.get("header"));
        sheet.setColumnWidth(4, 12 * 256);
        ExcelHelper.createCell(row, 5, "Postnummer", styles.get("header"));
        sheet.setColumnWidth(5, 11 * 256);
        ExcelHelper.createCell(row, 6, "Adresselinje 1", styles.get("header"));
        sheet.setColumnWidth(6, 15 * 256);
        ExcelHelper.createCell(row, 7, "Poststed", styles.get("header"));
        sheet.setColumnWidth(7, 12 * 256);
        ExcelHelper.createCell(row, 8, "Epost", styles.get("header"));
        sheet.setColumnWidth(8, 30 * 256);
        ExcelHelper.createCell(row, 9, "Tlf jobb", styles.get("header"));
        sheet.setColumnWidth(9, 12 * 256);
        ExcelHelper.createCell(row, 10, "Tlf mobil", styles.get("header"));
        sheet.setColumnWidth(10, 12 * 256);
        ExcelHelper.createCell(row, 11, "Medlemstype", styles.get("header"));
        sheet.setColumnWidth(11, 12 * 256);
        ExcelHelper.createCell(row, 12, "Delingsnivå", styles.get("header"));
        sheet.setColumnWidth(12, 12 * 256);
        ExcelHelper.createCell(row, 13, "Medlem siden", styles.get("header"));
        sheet.setColumnWidth(13, 12 * 256);
        ExcelHelper.createCell(row, 14, "Klubbnavn", styles.get("header"));
        sheet.setColumnWidth(14, 12 * 256);
        ExcelHelper.createCell(row, 15, "Postadresse", styles.get("header"));
        sheet.setColumnWidth(15, 12 * 256);
        ExcelHelper.createCell(row, 16, "Tilleggsinfo 2", styles.get("header"));
        sheet.setColumnWidth(16, 12 * 256);
    }

    private void addN3MiniRow(Person person, Date memberSince, HSSFSheet sheet, int rowNo, Map<String, CellStyle> styles) {
        Row row = sheet.createRow(rowNo);
        ExcelHelper.createCell(row, 0, person.getMembership() != null ? person.getMembership().getN3sportId() : "-1",
            styles.get("right"));
        ExcelHelper.createCell(row, 1, memberSince, styles.get("date"));
    }

    private void createN3MiniHeaders(Map<String, CellStyle> styles, HSSFSheet sheet) {
        Row row = sheet.createRow(0);
        row.setHeightInPoints(16);
        ExcelHelper.createCell(row, 0, "n3sportid", styles.get("header"));
        sheet.setColumnWidth(0, 15 * 256);
        ExcelHelper.createCell(row, 1, "Medlem siden", styles.get("header"));
        sheet.setColumnWidth(1, 12 * 256);
    }

}
